package scau.erps.test;

import java.sql.*;

/**
 * 初始化数据库
 * 创建数据库表、基本数据初始化等功能。
 */
public class DbInit {
	public static DbInit instance;
	public static final String DB_DRIVER = "com.mysql.jdbc.Driver";
	public static final String DB_URL = "jdbc:mysql://127.0.0.1/erpsdb";
	public static final String DB_USER = "root";
	public static final String DB_PASSWORD = "123456";
	public Connection conn = null;
	public Statement statement = null;
	public static String[] tables = new String[]{"balancesheet","bidding","comprehensivefee","factory","incomestatement","line","loan","material","orderset",
			"research","product","sand","score","steplimit","staff","store","systemorder","team","teamorder"};
	/**
	 * 实例化方法
	 */
	public static DbInit getInstance() {
		if (instance == null) {
			instance = new DbInit();
		}
		return instance;
	}

	/**
	 * 加载数据库驱动程序
	 */
	public DbInit() {
		try {
			Class.forName(DB_DRIVER).newInstance();
			this.conn = DriverManager.getConnection(DB_URL,DB_USER,DB_PASSWORD);
			statement = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_UPDATABLE);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	/**
	 * @Title 删除所有表格
	 */
	public void dropAllTable() {		
		for (int i = 0; i < tables.length; i++) {
			if (hasTable(tables[i]))
			dropTable(tables[i]);
		}		
	}

	/**
	 * 判断指定表是否存在
	 */
	public boolean hasTable(String tableName) {
		boolean flag = false;
		try {
			if (this.conn == null) {
				throw new SQLException("Connection has not been established!");
			}
			DatabaseMetaData meta = conn.getMetaData();
			ResultSet set = meta.getTables(null, null, tableName.toUpperCase(),
					null);
			while (set.next()) {
				flag = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			flag = false;
		}
		return flag;
	}

	/**
	 * 删除指定表
	 */
	public void dropTable(String tableName) {
		try {
			statement.execute("drop table " + tableName);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 建team表
	 */
	public void createTable_team() {
		String sql = "CREATE TABLE `team` ("
				+ "`ID` int(10) NOT NULL AUTO_INCREMENT,"
				+ "`TEAMNAME` varchar(30) NOT NULL,"
				+ "`PASSWORD` varchar(30) NOT NULL,"
				+ "PRIMARY KEY (`ID`)"
				+ ");";	
		try {
			statement.execute(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	
	/**
	 * 数据初始化
	 */
	public void dataInit(){
		String[] team = new String[8];
		team[0] = "INSERT INTO `team` VALUES ('1', '小组1', '123456');";
		team[1] = "INSERT INTO `team` VALUES ('2', '小组2', '123456');";
		team[2] = "INSERT INTO `team` VALUES ('3', '小组3', '123456');";
		team[3] = "INSERT INTO `team` VALUES ('4', '小组4', '123456');";
		team[4] = "INSERT INTO `team` VALUES ('5', '小组5', '123456');";
		team[5] = "INSERT INTO `team` VALUES ('6', '小组6', '123456');";
		team[6] = "INSERT INTO `team` VALUES ('7', '小组7', '123456');";
		team[7] = "INSERT INTO `team` VALUES ('8', '小组8', '123456');";		
		if (instance.hasTable("team")){
			try {
				for (int i = 0; i < team.length; i++) {
					statement.execute(team[i]);					
				}				
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		//重复添加新建表的数据；
	}

	/**
	 * 初始化
	 * @Description 实例化并创建表格
	 */
	public static void init() {
		DbInit instance = getInstance();
		instance.dropAllTable();		
		if (!instance.hasTable("team"))
			instance.createTable_team();
		// 添加语句创建其他表
	}
	public static void main(String[] args) {
		init();
		System.out.println("Database is ready!");
		instance.dataInit();
		System.out.println("Data is prepared!");		
	}
}
